package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;

import com.dudu.model.SmsTemplate;

import core.CoreDao;
import core.db.DBConnectionUtil;

/**
 * 模板
 * @author gaoxs
 *
 */
public class SmsTemplateDao {
	/**
	 * 查询所有模板
	 * @param coreDao
	 * @return
	 */
	public List<SmsTemplate> queryAll(CoreDao dao){
		Connection con = null;
		List<SmsTemplate> result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select * FROM t_sms_template ");
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] {null  });
			if (list != null && list.size() > 0) {
				result = dao.covertToBean(list, SmsTemplate.class);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 查询条数（分页用）
	 * @param paramSql
	 * @return
	 */
	public Integer getTotalAcount(CoreDao coreDao,String paramSql){
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" select count(1) as totalAcount "
					+ " from t_sms_template"
					+ " where 1=1  "
					+ paramSql );

			List<JSONObject> list = coreDao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalAcount");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 查询数据（分页）
	 * @param paramSql
	 * @param pageSql
	 * @return
	 */
	public List<JSONObject> queryAll(CoreDao coreDao,String paramSql,String pageSql){

		Connection con = null;
		List<JSONObject> result = null;
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}

		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" select *  from t_sms_template where 1=1"
					+ paramSql
					+ " order by create_time desc "
					+ pageSql );
			result = coreDao.serviceJsonSql(con, sql.toString(),
					new Object[] { null });

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 查询
	 * @param coreDao
	 * @param template_code
	 * @return
	 */
	public SmsTemplate query(CoreDao coreDao,String template_code){
		Connection con = null;
		SmsTemplate result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select * FROM t_sms_template where template_code=?");
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(),
					new Object[] { template_code });
			if (list != null && list.size() > 0) {
				List<SmsTemplate> items = coreDao.covertToBean(list, SmsTemplate.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	/**
	 * 查询
	 * @param coreDao
	 * @param template_code
	 * @return
	 */
	public SmsTemplate query(CoreDao coreDao,Integer id){
		Connection con = null;
		SmsTemplate result = null;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select * FROM t_sms_template where id=?");
			List<JSONObject> list = coreDao.serviceJsonSql(con, sql.toString(),
					new Object[] { id });
			if (list != null && list.size() > 0) {
				List<SmsTemplate> items = coreDao.covertToBean(list, SmsTemplate.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	public Integer insert(CoreDao coreDao,SmsTemplate bean){
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_sms_template(template_name, template_code, template_content, create_time, comments)");
			sql.append("VALUES(?,?,?,sysdate(),?)");

			List<JSONObject> list = coreDao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
							bean.getTemplate_name(),
							bean.getTemplate_code(),
							bean.getTemplate_content(),
							bean.getComments()
							});
			JSONObject obj = list.get(0);
			result = obj.optInt("result");
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	public Integer update(CoreDao coreDao,SmsTemplate bean){
		Connection con = null;
		int result = -1;

		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					" UPDATE t_sms_template set  template_name=?, template_content=?,comments=?");
			sql.append(" where id=?");

			List<JSONObject> list = coreDao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
							
							bean.getTemplate_name(),
							bean.getTemplate_content(),
							bean.getComments(),
							bean.getId()
						}

			);

			if (list != null && list.size() > 0) {
				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
}
